package com.returnsoft.security.eao;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;

import javax.ejb.LocalBean;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.TypedQuery;

import com.returnsoft.security.entity.Department;
import com.returnsoft.security.entity.Person;
import com.returnsoft.security.exception.EaoExcepcion;
import com.returnsoft.security.exception.SecurityExcepcion;

@LocalBean
@Stateless
public class PersonEao implements Serializable{
	private static final long serialVersionUID = 1L;
	@PersistenceContext
	private EntityManager em;

	public Person findPerson(Integer personId) throws EaoExcepcion {
		try {
			TypedQuery<Person> q = em.createQuery(
					"SELECT p FROM Person p WHERE p.id = :personId",
					Person.class);
			q.setParameter("personId", personId);
			Person person = q.getSingleResult();
			return person;
		} catch (NoResultException e) {
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}
	}

	public Person findByDocumentNumber(String documentNumber) throws EaoExcepcion {
		try {
			String query = "SELECT p FROM Person p WHERE p.documentNumber = :documentNumber";
			TypedQuery<Person> q = em.createQuery(query, Person.class);
			q.setParameter("documentNumber", documentNumber);
			Person person = q.getSingleResult();
			return person;
		} catch (NoResultException e) {
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}
	}

	public Person add(Person person) throws EaoExcepcion {
		try {
			person = em.merge(person);
			em.flush();
			return person;
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}

	}

	@SuppressWarnings("unchecked")
	public List<Person> findPersons(List<Integer> departmentsId,
			List<Integer> subDepartmentsId, String documentNumber, String firstname, String lastname)
			throws EaoExcepcion {
		
		
		try {

			String query = "SELECT p.id,p.documentNumber,p.firstname,p.lastname,"
					+ "d.id,d.name,dp.id,dp.name "
					+ "FROM Person p "
					+ "left join p.department d "
					+ "left join d.department dp "
					+ "WHERE p.id>0 ";

			if (departmentsId != null && departmentsId.size() > 0) {
				query += " and d.id in :departmentsId ";
			}

			if (subDepartmentsId != null && subDepartmentsId.size() > 0) {
				query += " and dp.id in :subDepartmentsId ";
			}

			if (documentNumber != null && !documentNumber.trim().equals("")) {
				query += " and p.documentNumber like :documentNumber ";
			}
			
			if (firstname != null && !firstname.trim().equals("")) {
				query += " and p.firstname like :firstname ";
			}
			
			if (lastname != null && !lastname.trim().equals("")) {
				query += " and p.lastname like :lastname ";
			}

			Query q = em.createQuery(query);

			if (departmentsId != null && departmentsId.size() > 0) {
				q.setParameter("departmentsId", departmentsId);
			}

			if (subDepartmentsId != null && subDepartmentsId.size() > 0) {
				q.setParameter("subDepartmentsId", subDepartmentsId);
			}

			if (documentNumber != null && !documentNumber.trim().equals("")) {
				q.setParameter("documentNumber", documentNumber+"%");
			}
			
			if (firstname != null && !firstname.trim().equals("")) {
				q.setParameter("firstname", "%"+firstname+"%");
			}
			
			if (lastname != null && !lastname.trim().equals("")) {
				q.setParameter("lastname", "%"+lastname+"%");
			}

			List<Object[]> personObjectList = (List<Object[]>) q
					.getResultList();

			List<Person> persons = new ArrayList<Person>();
			if (personObjectList.size() > 0) {
				for (Object[] personObject : personObjectList) {
					Integer personId = (Integer) personObject[0];
					String personDocumentNumber = (String) personObject[1];
					String personFirstname = (String) personObject[2];
					String personLastname = (String) personObject[3];
					Integer personDepartmentId = (Integer) personObject[4];
					String personDepartmentName = (String) personObject[5];
					Integer personDepartmentParentId = (Integer) personObject[6];
					String personDepartmentParentName = (String) personObject[7];

					Person person = new Person();
					person.setId(personId);
					person.setDocumentNumber(personDocumentNumber);
					person.setFirstname(personFirstname);
					person.setLastname(personLastname);
					if (personDepartmentId!=null) {
						Department department = new Department();
						department.setId(personDepartmentId);
						department.setName(personDepartmentName);
						if (personDepartmentParentId!=null) {
							Department departmentParent = new Department();
							departmentParent.setId(personDepartmentParentId);
							departmentParent.setName(personDepartmentParentName);
							department.setDepartment(departmentParent);
						}
						person.setDepartment(department);
					}
					persons.add(person);

				}
			}
			return persons;
		} catch (Exception e) {
			e.printStackTrace();
			throw new EaoExcepcion(e.getMessage());
		}
	}

}
